﻿using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Collections.Generic;
using System;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Record;
using System.Linq;
using Toxy;
using System.Data;
using DocumentFormat.OpenXml.Packaging;
using RtfPipe.Tokens;
using NPOI.SS.Formula.Functions;
using DocumentFormat.OpenXml.Spreadsheet;
using CellType = NPOI.SS.UserModel.CellType;
using IndexedColors = NPOI.SS.UserModel.IndexedColors;
using System.Runtime.CompilerServices;
using System.Diagnostics;
using CsvHelper;
using DocumentFormat.OpenXml.Wordprocessing;


namespace Growatt.CreateCCode
{
    public class SampleEXCEl
    {





       public static void ConverterAddStyle(string name)
        {
            var result = new List<(int, int)>();
            int start = 1;
            int end = 43;
            while (end <= 344)
            {
                result.Add((start, end));
                start = end + 1;
                end = start + 42;
            }

            foreach (var itemname in name.Split(','))
            {
                using (var fs = new FileStream($"{itemname}.xlsx", FileMode.Open, FileAccess.ReadWrite))
                {
                    var workbook = new XSSFWorkbook(fs);
                    var sheet = workbook.GetSheetAt(0);

                    var fill = (XSSFCellStyle)workbook.CreateCellStyle();
                    fill.FillForegroundColor = IndexedColors.Coral.Index;
                    fill.FillPattern = FillPattern.SolidForeground;

                    var fill2 = (XSSFCellStyle)workbook.CreateCellStyle();
                    fill2.FillForegroundColor = IndexedColors.LightGreen.Index;
                    fill2.FillPattern = FillPattern.SolidForeground;

                    var fill3 = (XSSFCellStyle)workbook.CreateCellStyle();
                    fill3.FillForegroundColor = IndexedColors.LightBlue.Index;
                    fill3.FillPattern = FillPattern.SolidForeground;

                    var fill4 = (XSSFCellStyle)workbook.CreateCellStyle();
                    fill4.FillForegroundColor = IndexedColors.BrightGreen.Index;
                    fill4.FillPattern = FillPattern.SolidForeground;

                    foreach (var items in result)
                    {
                        var lower_bound = items.Item1;
                        var upper_bound = items.Item2;

                        for (int col = 0; col < 5; col++)
                        {
                            var row = sheet.GetRow(lower_bound);
                            var cell = row.GetCell(col) ?? row.CreateCell(col);
                            cell.CellStyle = fill;
                        }

                        var row1 = sheet.GetRow(lower_bound + 1);
                        row1.GetCell(0).CellStyle = fill2;
                        row1.GetCell(1).CellStyle = fill3;

                        int IndexCount = 10;
                        for (int item = 1; item <= 4; item++)
                        {
                            IndexCount = 10 * item;
                            sheet.GetRow(lower_bound + IndexCount).GetCell(18).CellStyle = fill4;
                            sheet.GetRow(lower_bound + IndexCount).GetCell(19).CellStyle = fill4;
                            sheet.GetRow(lower_bound + IndexCount + 1).GetCell(0).CellStyle = fill2;
                            sheet.GetRow(lower_bound + IndexCount + 1).GetCell(1).CellStyle = fill3;
                        }

                        var row2 = sheet.GetRow(upper_bound - 1);
                        row2.GetCell(0).CellStyle = fill;
                        row2.GetCell(1).CellStyle = fill;
                    }

                    using (var fsOut = new FileStream($"{itemname}.xlsx", FileMode.Create, FileAccess.Write))
                    {
                        workbook.Write(fsOut);
                    }
                }
            }
        }



    












        //查找在sheet名称为总结构体的F2单元格中的字符串 在sheet名称为表结构体中列名为结构体变量中出现的次数 并将次数写在sheet名称为总结构体的F2的左边的单元中 查找在sheet名称为总结构体的F3单元格中的字符串 在sheet名称为表结构体中列名为结构体变量中出现的次数 并将次数写在sheet名称为总结构体的F3的左边的单元中  c#
        public static void ShowRowCount(string dataPath, int RowIndex, int ColunmIndex)
        {
            FileStream fs = new FileStream(dataPath, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = new XSSFWorkbook(fs);
            // 打开工作簿

            // 获取总结构体工作表
            var summarySheet = workbook.GetSheet("总结构体");

            // 获取表结构体工作表
            var tableSheet = workbook.GetSheet("表结构体");

            // 获取F2单元格
            var f2Cell = summarySheet.GetRow(RowIndex).GetCell(ColunmIndex); // F2的索引为1, 5

            // 获取F2单元格中的字符串
            var searchString = f2Cell.StringCellValue;

            // 统计在表结构体工作表中结构体变量列中出现的次数
            var count = 0;
            var rowCount = getAllRow(tableSheet) + 1; // 表结构体工作表的行数
            for (int i = 0; i < rowCount; i++)
            {
                var row = tableSheet.GetRow(i);
                var columnNameCell = row.GetCell(2); // 列名的索引是根据你的具体情况来确定的 (这个目前是固定的)

                if (columnNameCell.StringCellValue.Contains(searchString))
                {
                    count++;
                }
            }

            // 将次数写入总结构体工作表的左边单元格
            var countCell = summarySheet.GetRow(RowIndex).GetCell(ColunmIndex - 1); // 左边单元格的索引为1, 4
            Debug.WriteLine($"{searchString},当前获取的行数:" + count);
            countCell.SetCellValue(count);
            string newFilePath = dataPath;
            FileStream newFs = new FileStream(newFilePath, FileMode.Create, FileAccess.Write);
            workbook.Write(newFs);
            newFs.Close();
        }
        public static bool flagResult = true;



        public static bool HasDuplicatesInColumn(DataTable dt, string columnName)
        {
            // 创建一个HashSet来存储已经出现过的值
            HashSet<string> seenValues = new HashSet<string>();
            int count = 0;
            foreach (DataRow row in dt.Rows)
            {
                string value = row.Field<string>(columnName);

                if (seenValues.Contains(value))
                {
                    Console.WriteLine($"{dt.TableName}" + "行:" + count + ",列:" + columnName + "寄存器存在重复");
                    return true; // 发现重复值
                }
                seenValues.Add(value);
                count++;
            }

            return false; // 没有重复值
        }

        //遍历sheet名称为表结构体中的不包括第一行的每一行,根据每一行的H列作为Sheet名名称,根据每一行的E列作为这一行的最小值,根据每一行的F列作为这一行的最大值,H列作为Sheet名找到指定的sheet,并在找到的Sheet中遍历这个sheet的D列,判断D列的每个单元格是否在最大值最小值范围内,如果不在,则标红该单元格 c# NPIO
        public static void ShowRowRed(string dataPath)
        {

            string filePath = dataPath;
            FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = new XSSFWorkbook(fs);
            for (int sheetIndex = 0; sheetIndex < workbook.NumberOfSheets; sheetIndex++)
            {
                ISheet sheet = workbook.GetSheetAt(1);
                // 遍历每一行，跳过第一行
                for (int rowIndex = 1; rowIndex <= getAllRow(sheet); rowIndex++)
                {
                    IRow row = sheet.GetRow(rowIndex);
                    // 获取H列（7列）的值作为工作表名称
                    string sheetName = row.GetCell(7).ToString().Trim();
                    // 获取E列（4列）的值作为最小值
                    double minValue = double.Parse(row.GetCell(3).ToString());
                    // 获取F列（5列）的值作为最大值
                    double maxValue = double.Parse(row.GetCell(4).ToString());
                    // 根据sheetName查找指定的工作表
                    ISheet targetSheet = workbook.GetSheet(sheetName);
                    if (targetSheet != null)
                    {
                        // 遍历D列（3列）的单元格
                        for (int cellIndex = 1; cellIndex <= getAllRow(targetSheet); cellIndex++)
                        {
                            IRow targetRow = targetSheet.GetRow(cellIndex);
                            if (targetRow != null)
                            {
                                ICell cell = targetRow.GetCell(3);
                                if (cell != null)
                                {
                                    // 判断D列值是否在最大值和最小值范围内
                                    if (cell != null && double.TryParse(cell.ToString(), out double datas))
                                    {
                                        double cellValue = double.Parse(cell.ToString());
                                        if (cellValue < minValue || cellValue > maxValue)
                                        {
                                            // 标红该单元格
                                            ICellStyle redStyle = workbook.CreateCellStyle();
                                            redStyle.FillForegroundColor = IndexedColors.Red.Index;
                                            redStyle.FillPattern = FillPattern.SolidForeground;
                                            cell.CellStyle = redStyle;
                                            Console.WriteLine($"{sheetName}" + "行:" + cell.RowIndex + ",列:" + cell.ColumnIndex + "不在指定范围内");
                                            flagResult = false;
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            string newFilePath = dataPath;
            FileStream newFs = new FileStream(newFilePath, FileMode.Create, FileAccess.Write);
            workbook.Write(newFs);
            newFs.Close();

        }
        public static int getAllRow(ISheet sheet)
        {
            int rows = -1;
            try
            {
                for (int i = 0; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null && row.Cells.Count > 0 && row.Cells[0] != null && !string.IsNullOrEmpty(row.Cells[0].ToString())) // 检查行是否为空
                    {
                        rows++;
                    }
                }
                return rows;
            }
            catch (Exception ex)
            {
                Console.WriteLine("存在空行,请检查!");
                return rows;
            }
            // 遍历所有行
        }

        //获取sheet名称为表结构体中的H列的不为空且不包含注释字符的单元格,  遍历获取到的单元格并根据该单元格中的内容找到工作表中指定的sheet并获取sheet的行数 ,将行数写在sheet名称为表结构体的单元格的位置前两个单元格
        public static void ShowRowGetROWS(string dataPath)
        {
            FileStream file = new FileStream(dataPath, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = new XSSFWorkbook(file);
            ISheet sheet = workbook.GetSheet("表结构体");
            List<ICell> filteredCells = new List<ICell>();


            int columnIndex = 7; // H列的索引（从0开始）

            for (int i = 0; i <= getAllRow(sheet); i++)
            {
                IRow row = sheet.GetRow(i);
                if (row != null)
                {
                    ICell cell = row.GetCell(columnIndex);
                    if (cell != null && !string.IsNullOrEmpty(cell.ToString()))
                    {
                        filteredCells.Add(cell);
                        Debug.WriteLine("Value at row " + (i + 1) + " and column H: " + cell.StringCellValue);
                    }
                }
            }
            foreach (ICell cell in filteredCells)
            {
                string sheetName = cell.StringCellValue;
                ISheet targetSheet = workbook.GetSheet(sheetName);

                if (targetSheet != null)
                {
                    int rowCount = getAllRow(targetSheet) + 1;
                    // 将行数写入到对应表结构体的位置前两个单元格中
                    var row = sheet.GetRow(cell.RowIndex);
                    var cellData = row.GetCell(cell.ColumnIndex - 2); //获取前两个Cell

                    if (rowCount != 0)
                        cellData.SetCellValue(rowCount - 1);

                }
            }
            file.Close();
            FileStream outputFile = new FileStream(dataPath, FileMode.Create, FileAccess.Write);
            workbook.Write(outputFile);
            outputFile.Close();

        }

        public static void Rename(string dataPath)
        {
            FileStream file = new FileStream(dataPath, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = new XSSFWorkbook(file);
            ISheet sheet = workbook.GetSheet("表结构体");
            List<ICell> filteredCells = new List<ICell>();
            List<ICell> filteredCells2 = new List<ICell>();


            int columnIndex = 7; // H列的索引（从0开始） //获取所有表名称的列

            for (int i = 0; i <= getAllRow(sheet); i++)
            {
                IRow row = sheet.GetRow(i);
                if (row != null)
                {
                    ICell cell = row.GetCell(columnIndex);
                    if (cell != null && !string.IsNullOrEmpty(cell.ToString()))
                    {
                        filteredCells.Add(cell);
                    }
                }
            }

            int count = 0;
            foreach (ICell cell in filteredCells)
            {
                string sheetName = cell.StringCellValue;
                count++;
                ISheet targetSheet = workbook.GetSheet(sheetName);

                if (targetSheet != null)
                {
                    int colndex = 3; // 获取所有寄存器地址列的索引（从0开始）//
                    int colndex10 = 10; // 获取所有寄存器地址列的索引（从0开始）//

                    for (int i = 1; i <= getAllRow(targetSheet); i++)
                    {
                        IRow row = targetSheet.GetRow(i);
                        if (row != null)
                        {
                            ICell cellcol = row.GetCell(colndex);  //寄存器
                            ICell cellcol2 = row.GetCell(colndex10); //列名称
                            if (cellcol != null && !string.IsNullOrEmpty(cellcol.ToString()))
                            {
                                //if (!string.IsNullOrEmpty(cellcol2.StringCellValue) && cellcol2.StringCellValue.Trim() == "空")
                                //{
                                //    cellcol2.SetCellValue($"{sheetName}Register{cellcol}Cb");
                                //}

                                if (cellcol2 == null)
                                {
                                    cellcol2 = row.CreateCell(colndex10);
                                }
                                if (string.IsNullOrEmpty(cellcol2.StringCellValue))
                                {
                                    cellcol2.SetCellValue($"{sheetName}Register{cellcol}Cb");
                                }
                            }
                        }
                    }
                }
            }
            file.Close();
            FileStream outputFile = new FileStream(dataPath, FileMode.Create, FileAccess.Write);
            workbook.Write(outputFile);
            outputFile.Close();
        }
        static DataTable DataTableOrder(DataTable dataTable)
        {


            // 假设您已经有一个名为 "dataTable" 的 DataTable 对象
            // 假设第四列的列名为 "ColumnName4"

            // 使用 LINQ 查询对 DataTable 的每一行按照第四列的十进制大小进行排序
            var sortedRows = dataTable.AsEnumerable()
                .OrderBy(row => Convert.ToDecimal(row[dataTable.Columns[3].ColumnName]));

            // 创建一个新的 DataTable 对象来存储排序后的行
            DataTable sortedDataTable = dataTable.Clone(); // 复制原始 DataTable 的结构

            // 将排序后的行添加到新的 DataTable 中
            foreach (var row in sortedRows)
            {
                sortedDataTable.ImportRow(row);
            }


            return sortedDataTable;








            //// 假设您已经填充了 DataTable，这里省略填充数据的代码

            //// 按照第4列（索引为3）的数值进行升序排序
            //dt.DefaultView.Sort = dt.Columns[3].ColumnName + " ASC";





            //// 如果需要降序排序，可以使用以下代码
            //// dt.DefaultView.Sort = dt.Columns[3].ColumnName + " DESC";

            //// 获取排序后的结果
            //DataTable sortedDt = dt.DefaultView.ToTable();

            //return sortedDt;
            ////// 打印排序后的结果（仅用于示例目的）
            ////foreach (DataRow row in sortedDt.Rows)
            ////{
            ////    foreach (var item in row.ItemArray)
            ////    {
            ////        Console.Write(item + " ");
            ////    }
            ////    Debug.WriteLine();
            ////}
        }
        public static int GetCellCount(IRow row)
        {
            int cellCount = 0;

            if (row != null) // 检查行是否为空
            {
                foreach (ICell cell in row.Cells)
                {
                    if (cell.CellType != CellType.Blank)
                    {
                        cellCount++;
                    }
                }
            }
            return cellCount;
        }
        static List<DataTable> getDatatableFromExcel(string filePath)
        {

            List<DataTable> dataTables = new List<DataTable>();
            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                XSSFWorkbook workbook = new XSSFWorkbook(file);
                foreach (ISheet sheet in workbook)
                {
                    DataTable dt = new DataTable(sheet.SheetName);

                    IRow headerRow = sheet.GetRow(0);
                    foreach (ICell headerCell in headerRow)
                    {
                        dt.Columns.Add(headerCell.ToString());
                    }

                    for (int i = (sheet.FirstRowNum + 1); i <= getAllRow(sheet); i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dataRow = dt.NewRow();

                        for (int j = row.FirstCellNum; j < GetCellCount(row); j++)
                        {
                            if (row.GetCell(j) != null)
                            {
                                dataRow[j] = row.GetCell(j).ToString();
                            }
                        }

                        dt.Rows.Add(dataRow);
                    }
                    dataTables.Add(dt);
                    if (dataTables.Count == 2)
                    {
                        break;
                    }
                    // Use dt DataTable for further processing
                }
            }
            return dataTables;
        }

        static Dictionary<string, int> listDatatableCount = new Dictionary<string, int>();
        public static void ReOrderName(string filePath)
        {
            List<DataTable> dataTables = new List<DataTable>();
            List<DataTable> dataTablesResult = new List<DataTable>();
            dataTablesResult.AddRange(getDatatableFromExcel(filePath));//获取最开始两个表格
            var listTargetStrings = ReOrderName2(filePath);
            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                XSSFWorkbook workbook = new XSSFWorkbook(file);

                foreach (var nameSheet in listTargetStrings)
                {
                    DataTable dt = new DataTable(nameSheet);
                    ISheet sheet = workbook.GetSheet(nameSheet);
                    if (sheet != null)
                    {
                        dataTables.Add(dt);
                        IRow headerRow = sheet.GetRow(0);
                        foreach (ICell headerCell in headerRow)
                        {
                            dt.Columns.Add(headerCell.ToString());
                        }
                        listDatatableCount.Add(dt.TableName, dt.Columns.Count);

                        for (int i = (sheet.FirstRowNum + 1); i <= getAllRow(sheet); i++)
                        {
                            IRow row = sheet.GetRow(i);
                            DataRow dataRow = dt.NewRow();

                            for (int j = row.FirstCellNum; j < listDatatableCount[dt.TableName]; j++)
                            {
                                if (row.GetCell(j) != null)
                                {
                                    dataRow[j] = row.GetCell(j).ToString();
                                }
                            }
                            dt.Rows.Add(dataRow);
                        }
                    }
                }
            }

            foreach (var item in dataTablesResult)
            {
                // 检查排除前两列的值是否存在空值
                bool hasEmptyValue = item.Rows.Count==0?false:true;
                if (!hasEmptyValue)
                {
                    Console.WriteLine($"{item.TableName}:" + "行:"  + "为空");
                    SampleEXCEl.flagResult = false;
                }
               
            }
            foreach (var item in dataTables)
            {
                // 检查排除前两列的值是否存在空值
                bool hasEmptyValue = item.Rows.Count == 0 ? false : true;
                if (!hasEmptyValue)
                {
                    Console.WriteLine($"{item.TableName}:" + "行:" + "为空");
                    SampleEXCEl.flagResult = false;
                }

            }

            foreach (var item in dataTables)
            {
               var HasDuplicatesInColumn_re = HasDuplicatesInColumn(item, "寄存器地址");
                if (HasDuplicatesInColumn_re)
                {
                    SampleEXCEl.flagResult = false;
                }
                // 检查排序是否按照第二列的大小从小到大排序
                bool isSorted = true;
                for (int i = 1; i < item.Rows.Count; i++)
                {
                    if (item.Rows[i][3]!=null&&!string.IsNullOrEmpty(item.Rows[i][3].ToString())&&int.TryParse(item.Rows[i][3].ToString(),out int forward )&& 
                        item.Rows[i - 1][3] != null &&! string.IsNullOrEmpty(item.Rows[i-1][3].ToString()) && int.TryParse(item.Rows[i - 1][3].ToString(), out int next))
                    {
                        if (forward < next)
                        {
                            isSorted = false;
                            Console.WriteLine($"{item.TableName}:" + $"行:{i}" + "排序错误");
                            SampleEXCEl.flagResult = false;
                            break;
                        }
                    }  
                }

                // 输出结果
                if (!isSorted)
                    Console.WriteLine("排序错误");
            }

            foreach (var item in dataTables)
            {
                // 检查排除前两列的值是否存在空值
                bool hasEmptyValue = HasEmptyValueInColumns(item, 0, item.Columns.Count - 2);

                if (hasEmptyValue)
                {
                    SampleEXCEl.flagResult = false;
                }
            }
            //OutReorderxlsx(filePath, dataTablesResult);
        }

        static bool HasEmptyValueInColumns(DataTable dt, int startIndex, int endIndex)
        {
            int count = 0;

            foreach (DataRow row in dt.Rows)
            {
                for (int i = startIndex; i <= endIndex-1; i++)
                {
                    if (string.IsNullOrEmpty(row.Field<string>(i)))
                    {
                        Console.WriteLine($"{dt.TableName}:" + "行:" + count + ",列:" + i + "存在空值");
                        return true; // 存在空值
                    }
                }
            }

            return false; // 不存在空值
        }


        static void OutReorderxlsx(string filePath, List<DataTable> listDatatable)
        {
            using (FileStream file = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                XSSFWorkbook workbook = new XSSFWorkbook();

                // 将每个 DataTable 插入到 Excel 中
                foreach (var item in listDatatable)
                {
                    InsertDataTableToExcel(workbook, item);
                }

                workbook.Write(file);
            }
        }

        static void FontBold(XSSFWorkbook workbook, ICell cell)
        {

            // 设置单元格样式
            ICellStyle cellStyle = workbook.CreateCellStyle();
            IFont font = workbook.CreateFont();
            font.FontName = "微软雅黑";
            font.FontHeightInPoints = 11;
            cellStyle.SetFont(font);
            font.IsBold = true;
            cellStyle.Alignment = HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;

            // 将字体应用到单元格样式
            cellStyle.SetFont(font);

            cell.CellStyle = cellStyle;
        }

        static void InsertDataTableToExcel(XSSFWorkbook workbook, DataTable dt)
        {
            ISheet sheet = workbook.CreateSheet(dt.TableName);
            // 设置单元格样式
            ICellStyle cellStyle = workbook.CreateCellStyle();
            IFont font = workbook.CreateFont();
            font.FontName = "微软雅黑";
            font.FontHeightInPoints = 11;
            cellStyle.SetFont(font);


            // 创建表头
            IRow headerRow = sheet.CreateRow(0);
            foreach (DataColumn column in dt.Columns)
            {
                var cellsitem = headerRow.CreateCell(column.Ordinal);
                cellsitem.SetCellValue(column.ColumnName);
                FontBold(workbook, cellsitem);
            }

            // 填充数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {

                    var cellsitem = row.CreateCell(j);

                    cellsitem.SetCellValue(dt.Rows[i][j].ToString());
                    cellsitem.CellStyle = cellStyle;
                }
            }

            // 设置列宽自动调整
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
        }
        public static List<string> ReOrderName2(string dataPath)
        {
            FileStream file = new FileStream(dataPath, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = new XSSFWorkbook(file);
            ISheet sheet = workbook.GetSheet("表结构体");
            List<ICell> filteredCells = new List<ICell>();
            List<string> filteredCellsString = new List<string>();


            int columnIndex = 7; // H列的索引（从0开始）

            for (int i = 0; i <= getAllRow(sheet); i++)
            {
                IRow row = sheet.GetRow(i);
                if (row != null)
                {
                    ICell cell = row.GetCell(columnIndex);
                    if (cell != null && !string.IsNullOrEmpty(cell.ToString()))
                    {
                        filteredCells.Add(cell);
                    }
                }
            }
            foreach (ICell cell in filteredCells)
            {
                string sheetName = cell.StringCellValue;
                filteredCellsString.Add(sheetName);
            }
            file.Close();
            return filteredCellsString;
        }


    }
}
